#!/usr/bin/perl
use lib 'mods';
use db_config;
use ExploitFarm_SQL;
use strict;


my $posted_status = 2;
my $last_flag_delay = 5; #in minutes
my $db=new ExploitFarm_SQL($db_host,$db_port,$db_name,$db_login,$db_pass);
$db->connect();
my $dbh = $db->{'dbh'};

my (@row, @exploits, @teams);

my $sth = $dbh->prepare("SELECT fexploit from flags GROUP BY fexploit");
my $rv = $sth->execute;
while (@row = $sth->fetchrow_array){
    push @exploits, $row[0];
}

$sth = $dbh->prepare("SELECT fteam from flags GROUP BY fteam");
$rv = $sth->execute;
while (@row = $sth->fetchrow_array){
    push @teams, $row[0];
}


#--------------lets create and zero table with all data--------------

my %flags_amnt;
for my $team (@teams){
    for my $exploit (@exploits){
#    	$flags_amnt{$team} = {$exploit => 0};
	$flags_amnt{$team} -> {$exploit} = 0;
    }
} 


$sth = $dbh->prepare("SELECT fteam, fexploit, count (*) from flags GROUP BY fteam, fexploit");
$rv = $sth->execute;

while (@row = $sth->fetchrow_array){
    my $team = $row[0];
    my $exploit = $row[1];
    my $amnt = $row[2];
    $flags_amnt{$team}{$exploit} = $amnt;
}


#--------------lets create and zero table with posted data--------------

my %posted_flags_amnt;
for my $team (@teams){
    for my $exploit (@exploits){
#	$posted_flags_amnt{$team} = {$exploit => 0};
	$posted_flags_amnt{$team} -> {$exploit} = 0;
    }
} 


$sth = $dbh->prepare("SELECT fteam, fexploit, count (*) from flags WHERE fstatus=$posted_status GROUP BY fteam, fexploit");
$rv = $sth->execute;

while (@row = $sth->fetchrow_array){
    my $team = $row[0];
    my $exploit = $row[1];
    my $amnt = $row[2];
    $posted_flags_amnt{$team}{$exploit} = $amnt;
}


#--------------lets create and zero table with last data--------------

my %last_posted_flags_amnt;
for my $team (@teams){
    for my $exploit (@exploits){
#	$last_posted_flags_amnt{$team} = {$exploit => 0};
	$last_posted_flags_amnt{$team} -> {$exploit} = 0;	
    }
}

$sth = $dbh->prepare("SELECT fteam, fexploit, count (*) from flags WHERE fstatus=$posted_status AND ftime_statupd > now() - time '00:$last_flag_delay' GROUP BY fteam, fexploit");
$rv = $sth->execute;

while (@row = $sth->fetchrow_array){
    my $team = $row[0];
    my $exploit = $row[1];
    my $amnt = $row[2];
    $last_posted_flags_amnt{$team}{$exploit} = $amnt;
}





#-------------------lets make the table head---------------------

open STATS, ">getters/exploitstats.htm" or die "Can't open exploitstats.html $!\n";
print STATS <<"PREFIX";
<html>
<head>
    <title>Staticstics for exploits</title>
</head>
<meta http-equiv="refresh" content="2">
<body>
<table border="2" cellpadding="10" cellspacing="0">
    <thead align="center">
        <tr>
            <th>team</th>
            <th>flags total</th>
            <th>flags posted</th>
PREFIX

for (@exploits){
    print STATS "\t\t\t<th>$_</th>\n";
}
print STATS <<"PREFIX2";
	</tr>
    </thead>
    <tbody>
PREFIX2

#------------------lets make the table body----------------------


my $flags_total = 0;
my $posted_flags_total = 0;
for my $team (keys %flags_amnt){
    my $team_flags_total = 0;
    for (values %{$flags_amnt{$team}}){
	$team_flags_total += $_;
    }
    $flags_total += $team_flags_total;
    
    my $team_posted_flags_total = 0;
    for (values %{$posted_flags_amnt{$team}}){
	$team_posted_flags_total += $_;	
    }
    $posted_flags_total += $team_posted_flags_total;

    print STATS "\t\t<tr>\n";
    print STATS "\t\t\t<td>$team</td>\n";
    print STATS "\t\t\t<td>$team_flags_total</td>\n";
    print STATS "\t\t\t<td>$team_posted_flags_total</td>\n";
    
    
    for (@exploits){
	print STATS "\t\t\t<td>$posted_flags_amnt{$team}{$_}($last_posted_flags_amnt{$team}{$_})</td>\n";
    }
    

    print STATS "\t\t</tr>\n";
}
#-----------------lets make the table foot---------------------------
print STATS <<"FOOT";
	<tr>
    	    <td>total</td>
	    <td>$flags_total</td>
	    <td>$posted_flags_total</td>	
FOOT

for my $exploit (@exploits){
    my $exploit_total = 0;
    my $exploit_last_total = 0;
    for my $team (@teams){
	$exploit_total += $posted_flags_amnt{$team}{$exploit};
	$exploit_last_total += $last_posted_flags_amnt{$team}{$exploit};
    }
    print STATS "\t\t\t<td>$exploit_total($exploit_last_total)</td>\n";
}
print STATS "\t</tr>\n";

#-----------------finishing with table---------------------------

print STATS <<"POSTFIX";
    </tbody>
</table>
</body>
</html>
POSTFIX

close STATS;
